﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Collections;

namespace CarbonMeter
{
    class Database
    {
        static Database database = null;
        CarbonLinQDataContext context = null;
        public static Database getInstance()
        {
            if (database == null)
                database = new Database();
            return database;
        }
        private Database()
        {
            context = new CarbonLinQDataContext(CarbonMeter.Properties.Settings.Default.carbonConnectionString);
            
        }

        internal User verifyUser(int userid, string password)
        {
            var roles = (from e in context.Users
                         where e.userId == userid && e.active == true && e.password.ToLower().Equals(password.ToLower())
                         select new { e.role, e.name,e.surname,e.email});
            foreach (var e in roles)
            {
                User user = new User();
                user.active = true;
                user.role = e.role;
                user.surname = e.surname;
                user.userId = userid;
                user.name = e.name;
                user.email = e.email;
                return user;
            }
            return null;
        }
        public void Close()
        {
            try 
	        {
                context.Dispose();
	        }
	        catch (Exception)
	        {
		
		        
	        }
        }

        internal bool DatabaseExists()
        {
            return context.DatabaseExists();
        }

        public object getProducts(int id)
        {
           return  (from p in context.Products
                         where p.groupId == id
                         select new { p.productId, p.productName }).ToList();
        }

        internal object getProductUnits(int productId)
        {
            return (from p in context.ProductUnits
                    where p.productId == productId
                    select new { p.unitName }).ToList();
        }

        internal FossilFuelConstant getFossilFuelConstant(int productId)
        {
            var results = (from p in context.FossilFuelConstants
                    where p.productId == productId
                    select new { p.kwhIn, p.kwhOut,p.litreIn, p.litreOut,p.toeIn,p.toeOut,p.tonIn, p.tonOut,p.uncertainty });
            
            FossilFuelConstant fs = new FossilFuelConstant();
            fs.productId = productId;
            foreach ( var e in results)
            {
                fs.toeIn = e.toeIn;
                fs.toeOut = e.toeOut;

                fs.tonIn = e.tonIn;
                fs.tonOut = e.tonOut;

                fs.litreIn = e.litreIn;
                fs.litreOut = e.litreOut;

                fs.kwhIn = e.kwhIn;
                fs.kwhOut = e.kwhOut;

                fs.uncertainty = e.uncertainty;
            }

            return fs;
        }

        internal void addFossilFuelConsumption(int p, DateTime dateTime, bool inProcess, int avg, string unit, float amount, float carbon)
        {
            try
            {
                FossilFuelConsumption record = new FossilFuelConsumption();
                record.userId = MainForm.user.userId;
                record.productId = p;
                record.amount = amount;
                if(avg != -1)
                record.averageEfficiency = avg;
                record.unitName = unit;
                record.carbonAmount = carbon;
                record.inProcess = inProcess;
                record.billDate = dateTime;
                record.insertedDate = DateTime.Now;

                context.FossilFuelConsumptions.InsertOnSubmit(record);
                context.SubmitChanges();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }
        internal void updateFossilFuelConsumption(int recordId, int productId, DateTime dateTime, bool inProcess, int avg, string unit, float amount, float carbon)
        {
            try
            {
     
                FossilFuelConsumption record = (from p in context.FossilFuelConsumptions
                               where p.recordId == recordId
                               select p).Single();

                record.userId = MainForm.user.userId;
                record.productId = productId;
                record.amount = amount;
                record.averageEfficiency = avg;
                if (avg == -1)
                    record.averageEfficiency = null;
                record.unitName = unit;
                record.carbonAmount = carbon;
                record.inProcess = inProcess;
                record.billDate = dateTime;
                record.insertedDate = DateTime.Now;

               
                context.SubmitChanges();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        internal object getFossilFuelConsumptionByUserRecordAndTime(int userId, DateTime dateTime)
        {
            var results = (from p in context.FossilFuelConsumptions
                           where p.userId == userId && p.insertedDate.Value.DayOfYear == dateTime.DayOfYear
                           select new { p.recordId, p.productId , p.unitName, p.amount, p.averageEfficiency, p.carbonAmount, p.billDate,p.inProcess, p.insertedDate});
            return results;
        }
        internal object getFossilFuelConsumptionsForReport(String title)
        {
           //var res = (from p in context.FossilFuelConsumptions
             //         select new {p.recordId ,p.amount, p.billDate, p.carbonAmount });

           if (title.Equals("Yıl"))
           {
               return  (from e in context.FossilFuelConsumptions group e by new { Year = e.billDate.Year } into g 
                         select new { billDate = new DateTime(g.Key.Year,1,1), 
                   amount = g.Sum(a => a.amount), carbonAmount = g.Sum(a => a.carbonAmount) });
           }
           else if (title.Equals("Ay"))
           {
               return (from e in context.FossilFuelConsumptions
                         group e by new { Year = e.billDate.Year, Month = e.billDate.Month } into g
                         select new
                         {
                             billDate = new DateTime(g.Key.Year, g.Key.Month, 1),
                             amount = g.Sum(a => a.amount),
                             carbonAmount = g.Sum(a => a.carbonAmount)
                         });
           }
           else if (title.Equals("Hafta"))
           {
               return (from e in context.FossilFuelConsumptions
                       group e by new { Year = e.billDate.Year, Week = e.billDate.DayOfYear/7 } into g
                       select new
                       {
                           billDate = new DateTime(g.Key.Year, g.Key.Week / 4, 1),
                           amount = g.Sum(a => a.amount),
                           carbonAmount = g.Sum(a => a.carbonAmount)
                       });
           }
           else if (title.Equals("Gün"))
           {
               return (from e in context.FossilFuelConsumptions
                       group e by new { Year = e.billDate.Year, Month = e.billDate.Month, Day = e.billDate.Day } into g
                       select new
                       {
                           billDate = new DateTime(g.Key.Year, g.Key.Month, g.Key.Day),
                           amount = g.Sum(a => a.amount),
                           carbonAmount = g.Sum(a => a.carbonAmount)
                       });
           }
           return null;
        }
       /* internal ArrayList getFossilFuelConsumptionsForReport()
        {
            var res = (from p in context.FossilFuelConsumptions
                       select new { p.carbonAmount, p.insertedDate });

            ArrayList result = new ArrayList();
            foreach (var item in res)
            {
                result.Add(new object[] { item.insertedDate, item.carbonAmount });
            }

            return result;
        }*/

        internal string getProductGroupName(int id)
        {
            var results = (from p in context.ProductGroups
                           where p.groupId == id
                           select new { p.groupName }).Single();
            return results.groupName;
        }

        internal PeopleTransportationConstant getPeopleTransportationConstant(int productId)
        {
            try
            {
                var e = (from p in context.PeopleTransportationConstants
                           where p.productId == productId
                           select new { p.kgCEqPerKmEmpty, p.kgCeqPerKmWithoutProcess, p.kgCeqPerKmWithProcess,p.kgCEqPerKmFull,p.kgCEqPerKmManufacture,
                              p.kgCEqPerKmMixed,p.kgCEqPerKmNonUrban,p.kgCEqPerKmNotKyoto,
                               p.kgCEqPerKmUrban,p.productId,p.uncertainty }).Single();

                PeopleTransportationConstant fs = new PeopleTransportationConstant();
                fs.productId = productId;
                fs.kgCEqPerKmEmpty = e.kgCEqPerKmEmpty;
                fs.kgCeqPerKmWithoutProcess = e.kgCeqPerKmWithoutProcess;
                fs.kgCeqPerKmWithProcess = e.kgCeqPerKmWithProcess;
                fs.kgCEqPerKmFull = e.kgCEqPerKmFull;
                fs.kgCEqPerKmManufacture = e.kgCEqPerKmManufacture;
                fs.kgCEqPerKmMixed = e.kgCEqPerKmMixed;
                fs.kgCEqPerKmNonUrban = e.kgCEqPerKmNonUrban;
                fs.kgCEqPerKmNotKyoto = e.kgCEqPerKmNotKyoto;
                fs.kgCEqPerKmUrban = e.kgCEqPerKmUrban;
           

            return fs;
            }
            catch (Exception)
            {

                return null;
            }
        }

        internal void addPeopleTransportationConsumption(int productId, float distance, int inkyoto, float carbon, DateTime dateTime)
        {
            try
            {
                PeopleTransportationConsumption record = new PeopleTransportationConsumption();
                record.userId = MainForm.user.userId;
                record.productId = productId;
                record.carbonAmount = carbon;
                record.distanceKm = distance;
                if (inkyoto != -1)
                    record.inKyoto = (inkyoto == 0 ? false : true);
                record.billDate = dateTime;
                record.insertedDate = DateTime.Now;

                context.PeopleTransportationConsumptions.InsertOnSubmit(record);
                context.SubmitChanges();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        internal object getPeopleTransportationConsumptionByUserRecordAndTime(int userId, DateTime dateTime)
        {
            var results = (from p in context.PeopleTransportationConsumptions
                           where p.userId == userId && p.insertedDate.DayOfYear == dateTime.DayOfYear
                           select new { p.recordId,p.userId, p.productId, p.carbonAmount, p.billDate,p.distanceKm, p.inKyoto, p.insertedDate });
            return results;
        }
        internal object getRoadFreightConsumptionByUserAndTime(int userId, DateTime dateTime)
        {
            var results = (from p in context.RoadFreightConsumptions
                           where p.userId == userId && p.insertedDate.DayOfYear == dateTime.DayOfYear
                           select new {p.plate, p.recordId, p.userId,  p.distanceKm, p.carbonAmount, p.billDate, p.insertedDate });
            return results;
        }

        internal object getFreightConsumptionByUserAndTime(int userId, int groupId, DateTime dateTime)
        {
            //TODO: buradaki grup numarasi ve grupId leri dogru girilmeli
            //NOT : burada table merge etme islemi var
            if (groupId == 1)    //hava
            {
                var results = (from p in context.FreightConsumptions
                               where p.userId == userId && p.insertedDate.DayOfYear == dateTime.DayOfYear && (p.Product.groupId == 15)
                               select new {p.plate, p.recordId, p.userId,   p.amountKg, p.amountM3, p.transportationDays, p.transportationDistance, p.inKyoto, p.carbonAmount, p.billDate, p.insertedDate });
                return results;

            }
            else if (groupId == 2)   //deniz
            {
                var results = (from p in context.FreightConsumptions
                               where p.userId == userId && p.insertedDate.DayOfYear == dateTime.DayOfYear && (p.Product.groupId == 11 || p.Product.groupId == 12 || p.Product.groupId == 13 || p.Product.groupId == 14)
                               select new { p.plate, p.recordId, p.userId , p.amountKg, p.amountM3, p.transportationDays, p.transportationDistance, p.inKyoto, p.carbonAmount, p.billDate, p.insertedDate });
                return results;

            }
            else  //nehir
            {
                var results = (from p in context.FreightConsumptions
                               where p.userId == userId && p.insertedDate.DayOfYear == dateTime.DayOfYear && (p.Product.groupId == 16)
                               select new { p.plate, p.recordId, p.userId , p.amountKg, p.amountM3, p.transportationDays, p.transportationDistance, p.inKyoto, p.carbonAmount, p.billDate, p.insertedDate });
                return results;
            }


        }

        internal RoadFreightConstant getRoadFreightConstant(int productId)
        {
            var results = (from p in context.RoadFreightConstants
                           where p.productId == productId
                           select new { p.kgCEqKmManufacture, p.kgCEqKmUnloadedTruck, p.kgCEqKmVehicleFull, p.uncertainty });

            RoadFreightConstant roadConst = new RoadFreightConstant();
            roadConst.productId = productId;
            foreach (var e in results)
            {
                roadConst.kgCEqKmManufacture = e.kgCEqKmManufacture;
                roadConst.kgCEqKmUnloadedTruck = e.kgCEqKmUnloadedTruck;
                roadConst.kgCEqKmVehicleFull = e.kgCEqKmVehicleFull;
                roadConst.uncertainty = e.uncertainty;
            }

            return roadConst;
        }

        internal void addRoadFreightConsumption(int productId, float distance, float carbonAmount, DateTime billDate, string plate)
        {
            try
            {
                RoadFreightConsumption record = new RoadFreightConsumption();
                record.userId = MainForm.user.userId;
                record.productId = productId;
                record.distanceKm = distance;
                record.carbonAmount = carbonAmount;
                record.billDate = billDate;
                record.insertedDate = DateTime.Now;
                record.plate = plate;
                context.RoadFreightConsumptions.InsertOnSubmit(record);
                context.SubmitChanges();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        internal void updateRoadFreightConsumption(int recordId, int productId, float distance, float carbonAmount,
            DateTime billDate, string plate)
        {
            try
            {

                RoadFreightConsumption record = (from p in context.RoadFreightConsumptions
                                                 where p.recordId == recordId
                                                 select p).Single();

                record.userId = MainForm.user.userId;
                record.productId = productId;
                record.distanceKm = distance;
                record.carbonAmount = carbonAmount;
                record.billDate = billDate;
                record.insertedDate = DateTime.Now;
                record.plate = plate;
                context.SubmitChanges();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        internal FreightConstant getFreightConstant(int productId)
        {
            var results = (from p in context.FreightConstants
                           where p.productId == productId
                           select new
                           {
                               p.numberOfContainer,
                               p.kgCEqPerTonKm,
                               p.kgCEqPerTonKmNotIncKyoto,
                               p.capacityM3,
                               p.tonsOfFuelPerDay,
                               p.tonsCarried,
                               p.uncertainty
                           });

            FreightConstant freightConst = new FreightConstant();
            freightConst.productId = productId;
            foreach (var e in results)
            {
                freightConst.kgCEqPerTonKm = e.kgCEqPerTonKm;
                freightConst.kgCEqPerTonKmNotIncKyoto = e.kgCEqPerTonKmNotIncKyoto;
                freightConst.tonsCarried = e.tonsCarried;
                freightConst.tonsOfFuelPerDay = e.tonsOfFuelPerDay;
                freightConst.uncertainty = e.uncertainty;
            }

            return freightConst;
        }

        internal void addFreightConsumption(int productId, float yukInTons, float yukHacim, 
            float seyahatSuresi, float distance, int kyotoKapsaminda, float carbonAmount, DateTime billDate, string plate)
        {

            try
            {
                FreightConsumption record = new FreightConsumption();
                record.userId = MainForm.user.userId;
                record.productId = productId;
                record.plate = plate;
                record.amountKg = yukInTons;
                if (yukHacim != -1)
                    record.amountM3 = yukHacim;
                if (seyahatSuresi != -1)
                    record.transportationDays = seyahatSuresi;
                if (distance != -1)
                    record.transportationDistance = distance;
                if (kyotoKapsaminda == 0)
                    record.inKyoto = false;
                else if (kyotoKapsaminda == 1)
                    record.inKyoto = true;
                record.carbonAmount = carbonAmount;
                record.billDate = billDate;
                record.insertedDate = DateTime.Now;

                context.FreightConsumptions.InsertOnSubmit(record);
                context.SubmitChanges();
            }
            catch (Exception er)
            {

                MessageBox.Show(er.Message);
            }

        }

        internal void updateFreightConsumption(int recordId, int productId, float amountKg,
            float yukHacim, float seyahatSuresi, float distance, int kyotoKapsaminda, float carbonAmount,
            DateTime billDate, string plate)
        {
            try
            {

                FreightConsumption record = (from p in context.FreightConsumptions
                                             where p.recordId == recordId
                                             select p).Single();

                record.userId = MainForm.user.userId;
                record.productId = productId;
                record.amountKg = amountKg;
                record.amountM3 = yukHacim;
                record.transportationDays = seyahatSuresi;
                record.transportationDistance = distance;
                if (kyotoKapsaminda == 0)
                    record.inKyoto = false;
                else if (kyotoKapsaminda == 1)
                    record.inKyoto = true;
                record.carbonAmount = carbonAmount;
                record.billDate = billDate;
                record.insertedDate = DateTime.Now;
                record.plate = plate;
                context.SubmitChanges();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        internal int getGroupId(int productId)
        {
            var results = (from p in context.Products
                           where p.productId == productId
                           select new { p.groupId }).Single();

            return results.groupId;

        }

        internal object getProductsFreight(int grupNo)
        {
            //TODO: buradaki grup numarasi ve grupId leri dogru girilmeli
            if (grupNo == 0)    //kara
            {
                return (from p in context.Products
                        where (p.groupId == 10)
                        select new { p.productId, p.productName }).ToList();
            }
            else if (grupNo == 1)    //hava
            {
                return (from p in context.Products
                        where (p.groupId == 15)
                        select new { p.productId, p.productName }).ToList();
            }
            else if (grupNo == 2)    //deniz
            {
                return (from p in context.Products
                        where (p.groupId == 11 || p.groupId == 12 || p.groupId == 13 || p.groupId == 14)
                        select new { p.productId, p.productName }).ToList();
            }
            else
            {
                return (from p in context.Products  //nehir
                        where (p.groupId == 16)
                        select new { p.productId, p.productName }).ToList();
            }
        }

        internal object getPeopleTransportationConsumptionsForReport()
        {
            var res = (from p in context.PeopleTransportationConsumptions
                       select new { p.recordId, p.distanceKm, p.billDate, p.carbonAmount });
            return res;
        }

        internal object getNakliyeConsumptionsForReport()
        {
            var res = (from p in context.FreightConsumptions
                       select new { p.recordId, p.amountKg, p.transportationDistance, p.transportationDays, p.billDate, p.carbonAmount });


            return res;
        }
    }
}
